﻿using System;
using System.Data;
using System.Collections;
using CE.Database;

/// <summary>
/// Summary description for ProposalSummaryDB
/// </summary>
/// 
namespace CE.CarInsurance
{
    public class ProposalSummaryDB
    {
        public DataTable GetCompletedApplicationSummary(Int64 ProviderCompanyId, string InsuranceType, Int16 ReportType, string ReportDate1, string ReportDate2, string RefCEUserId, Int64 CEID)
        {
            MyDatabase _db = new MyDatabase("GGR_CRMDBConnectionString");
            string _spName = "[sprocCXTHProposalSummaryReportSelectedList]";
            SortedList _sPara = new SortedList();

            _sPara.Add("@ProviderCompanyId", ProviderCompanyId);
            _sPara.Add("@InsuranceType", InsuranceType);
            _sPara.Add("@ReportType", ReportType);
            _sPara.Add("@ReportDate1", ReportDate1);
            _sPara.Add("@ReportDate2", ReportDate2);
            _sPara.Add("@RefCEUserId", RefCEUserId);
            _sPara.Add("@CEID", CEID);
            
            return _db.GetDataTableBySP(_spName, _sPara);
        }

        public DataTable GetPendingApplicationSummary(Int64 ProviderCompanyId, string InsuranceType, Int16 ReportType, string ReportDate1, string ReportDate2, string RefCEUserId, Int64 CEID, string PaymentType)
        {
            MyDatabase _db = new MyDatabase("GGR_CRMDBConnectionString");
            string _spName = "[sprocCXTHPendingProposalSummaryReportSelectedList]";
            SortedList _sPara = new SortedList();

            _sPara.Add("@ProviderCompanyId", ProviderCompanyId);
            _sPara.Add("@InsuranceType", InsuranceType);
            _sPara.Add("@ReportType", ReportType);
            _sPara.Add("@ReportDate1", ReportDate1);
            _sPara.Add("@ReportDate2", ReportDate2);
            _sPara.Add("@RefCEUserId", RefCEUserId);
            _sPara.Add("@CEID", CEID);
            _sPara.Add("@PaymentType", PaymentType);

            return _db.GetDataTableBySP(_spName, _sPara);
        }

        public DataTable GetPaymentCancelSummary(Int64 ProviderCompanyId, string InsuranceType, Int16 ReportType, string ReportDate1, string ReportDate2, string RefCEUserId, Int64 CEID, string PaymentType, Int16 PaymentCode)
        {
            MyDatabase _db = new MyDatabase("GGR_CRMDBConnectionString");
            string _spName = "[sprocCXTHPendingProposalSummaryReportSelectedList]";
            SortedList _sPara = new SortedList();

            _sPara.Add("@ProviderCompanyId", ProviderCompanyId);
            _sPara.Add("@InsuranceType", InsuranceType);
            _sPara.Add("@ReportType", ReportType);
            _sPara.Add("@ReportDate1", ReportDate1);
            _sPara.Add("@ReportDate2", ReportDate2);
            _sPara.Add("@RefCEUserId", RefCEUserId);
            _sPara.Add("@CEID", CEID);
            _sPara.Add("@PaymentType", PaymentType);
            _sPara.Add("@PaymentCode", PaymentCode);

            return _db.GetDataTableBySP(_spName, _sPara);
        }

        public DataSet GetAXAApplication(Int64 RefId, string InsType, Int16 IsInternal)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocReportApplicationSelectItem_AXA]";

            SortedList _spara = new SortedList();
            _spara.Add("@RefId", RefId);
            _spara.Add("@InsuranceType", InsType);
            _spara.Add("@IsInternal", IsInternal);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public DataSet GetAZCpApplication(Int64 RefId, string InsType, Int16 IsInternal)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocReportApplicationSelectItem_AZCp]";

            SortedList _spara = new SortedList();
            _spara.Add("@RefId", RefId);
            _spara.Add("@InsuranceType", InsType);
            _spara.Add("@IsInternal", IsInternal);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public DataSet GetBKIApplication(Int64 RefId, string InsType, Int16 IsInternal)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocReportApplicationSelectItem_BKI]";

            SortedList _spara = new SortedList();
            _spara.Add("@RefId", RefId);
            _spara.Add("@InsuranceType", InsType);
            _spara.Add("@IsInternal", IsInternal);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public DataSet GetChartisApplication(Int64 RefId, string InsType, Int16 IsInternal)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocReportApplicationSelectItem_Chartis]";

            SortedList _spara = new SortedList();
            _spara.Add("@RefId", RefId);
            _spara.Add("@InsuranceType", InsType);
            _spara.Add("@IsInternal", IsInternal);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public DataSet GetLMGApplication(Int64 RefId, string InsType, Int16 IsInternal)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocReportApplicationSelectItem_LMG]";

            SortedList _spara = new SortedList();
            _spara.Add("@RefId", RefId);
            _spara.Add("@InsuranceType", InsType);
            _spara.Add("@IsInternal", IsInternal);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public DataSet GetMSIGApplication(Int64 RefId, string InsType, Int16 IsInternal)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocReportApplicationSelectItem_MSIG]";

            SortedList _spara = new SortedList();
            _spara.Add("@RefId", RefId);
            _spara.Add("@InsuranceType", InsType);
            _spara.Add("@IsInternal", IsInternal);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public DataSet GetMTIApplication(Int64 RefId, string InsType, Int16 IsInternal)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocReportApplicationSelectItem_MTI]";

            SortedList _spara = new SortedList();
            _spara.Add("@RefId", RefId);
            _spara.Add("@InsuranceType", InsType);
            _spara.Add("@IsInternal", IsInternal);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public DataSet GetKurniaApplication(Int64 RefId, string InsType, Int16 IsInternal)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocReportApplicationSelectItem_Kurnia]";

            SortedList _spara = new SortedList();
            _spara.Add("@RefId", RefId);
            _spara.Add("@InsuranceType", InsType);
            _spara.Add("@IsInternal", IsInternal);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public DataSet GetTMSApplication(Int64 RefId, string InsType, Int16 IsInternal)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocReportApplicationSelectItem_TMS]";

            SortedList _spara = new SortedList();
            _spara.Add("@RefId", RefId);
            _spara.Add("@InsuranceType", InsType);
            _spara.Add("@IsInternal", IsInternal);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public Int64 UpdatePaymentStatus(Int64 CarId, Int64 ProviderId, string ReceiptNo, bool PaymentStatus, Int64 UserId)
        {
            MyDatabase _db = new MyDatabase("GGR_CRMDBConnectionString");
            Int64 _result = -1;
            string _spName = "sprocCXTHReportsUpdatePaymentStatusSingleItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@CECarId", CarId);
            _sPara.Add("@ProviderCompanyId", ProviderId);
            _sPara.Add("@ReceiptNo", ReceiptNo);
            _sPara.Add("@PaymentStatus", PaymentStatus);
            _sPara.Add("@AgentId", UserId);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

        public Int64 UpdatePaymentStatus(ProposalSummary proposalsummary)
        {
            MyDatabase _db = new MyDatabase("GGR_CRMDBConnectionString");
            Int64 _result = -1;
            string _spName = "sprocCXTHReportsUpdatePaymentStatusSingleItem";

            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@CECarId", proposalsummary.CECarId);
            _sPara.Add("@ProviderCompanyId", proposalsummary.ProviderCompanyId);
            //_sPara.Add("@ReceiptNo", proposalsummary.ReceiptNo);
            _sPara.Add("@PaymentStatus", proposalsummary.PaymentStatus);
            _sPara.Add("@AgentId", proposalsummary.AgentId);
            _sPara.Add("@Info10", proposalsummary.Info10);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public DataSet GetCarApplicationDetails(Int64 CarId)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocReportApplicationSelectItembyCarId]";

            SortedList _spara = new SortedList();
            _spara.Add("@CarId", CarId);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public DataSet GetCompletedUserItem(Int64 CarId, Int64 ProviderCompanyId)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocCarInsuranceTHCompletedUserSelectItem]";

            SortedList _spara = new SortedList();
            _spara.Add("@CarId", CarId);
            _spara.Add("@ProviderCompanyId", ProviderCompanyId);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public Int64 UpdatePaymentType(string OrderRefNo, Int64 ProviderId, string PaymentType, Int64 AgentId)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCXCRMChangePaymentTypeSelectItembyAgent";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@OrderRefNo", OrderRefNo);
            _sPara.Add("@ProviderCompanyId", ProviderId);
            _sPara.Add("@PaymentType", PaymentType);
            _sPara.Add("@AgentId", AgentId);
            //_sPara.Add("@WorkType", WorkType);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

        public Int64 UpdateCreditCardPaymentStatus(string OrderRefNo, Int64 ProviderId, string ReceiptNo, Int64 AgentId, Int32 WorkType)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCXCRMiPayPaymentStatusUpdatedSelectItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@OrderRefNo", OrderRefNo);
            _sPara.Add("@ProviderCompanyId", ProviderId);
            _sPara.Add("@ReceiptNo", ReceiptNo);
            _sPara.Add("@AgentId", AgentId);
            _sPara.Add("@WorkType", WorkType);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

        public Int64 UpdateBankInPaymentStatus(string OrderRefNo, Int64 ProviderId, string ReceiptNo, Int64 AgentId, Int32 WorkType)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCXCRMBankInPaymentStatusUpdatedSelectItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@OrderRefNo", OrderRefNo);
            _sPara.Add("@ProviderCompanyId", ProviderId);
            _sPara.Add("@ReceiptNo", ReceiptNo);
            _sPara.Add("@AgentId", AgentId);
            _sPara.Add("@WorkType", WorkType);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

        public Int64 UpdateApplicationCancelStatus(string OrderRefNo, Int64 ProviderId, Int64 AgentId, Int16 Step, string Remarks)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCXCRMApplicationCancelUpdateSelectItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@OrderRefNo", OrderRefNo);
            _sPara.Add("@ProviderCompanyId", ProviderId);
            _sPara.Add("@AgentId", AgentId);
            _sPara.Add("@Step", Step);
            _sPara.Add("@Remarks", Remarks);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

        public Int64 UpdateApplicationRetrieveStatus(string OrderRefNo, Int64 ProviderId, Int64 AgentId, Int16 Step)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCXCRMApplicationCancelUpdateSelectItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@OrderRefNo", OrderRefNo);
            _sPara.Add("@ProviderCompanyId", ProviderId);
            _sPara.Add("@AgentId", AgentId);
            _sPara.Add("@Step", Step);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

    }
}